package com.titan.core.utils.db;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobCreator;

import com.titan.core.utils.dbinfo.dialect.DBDialect;

/**
 * @author 周成功
 * @email 86898837@qq.com
 * @date 2020年02月14日
 */
public class DBUtils {
    private DBUtils() {
        // 该类不需要实例化
    }

    
    //获取设置日志
    private static Log logDebug = LogFactory.getLog(DBUtils.class);

    /**
     * 在JNDI中获得数据源对象。
     * @param dsn String
     * @return DataSource
     * @throws NamingException
     */
    public static DataSource getDataSource(String dsn)throws NamingException{
        try {
//            String beanid = dsn;
//            DataSource ds = (DataSource)SpringUtils.getBeanFactory().getBean(beanid);
//            if(ds!=null) {
//                return ds;
//            }
        } catch(Exception e) {}
        
        Context ctx = new InitialContext();
        DataSource ds = null;
        try {
            ds = (DataSource) ctx.lookup(dsn);
        } catch (NamingException ex) {
            Context envContext = (Context) ctx.lookup("java:/comp/env");
            ds = (DataSource) envContext.lookup(dsn);
        }
        return ds;
    }    
    
    
    // 注册JDBC驱动
    public static void registerDriver(String dbDriver) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        Driver driver = (Driver) Class.forName(dbDriver).newInstance();
        DriverManager.registerDriver(driver);
    }

    /**
     * 向数据库建立一个链接
     * @param dsn String
     * @param isAuto boolean
     * @throws Exception
     * @return Connection
     */
    public static Connection getConnection(String dsn, boolean isAuto)throws SQLException,NamingException {
        Connection connection = getDataSource(dsn).getConnection();
        connection.setAutoCommit(isAuto);
        return connection;
    }
    public static Connection getConnection(String dsn) throws SQLException,NamingException {
        return getDataSource(dsn).getConnection();
    }    
    public static Connection getConnection(String url, String userid, String password)throws SQLException,ClassNotFoundException, InstantiationException, IllegalAccessException{
        return DriverManager.getConnection(url, userid, password);
    }
    public static Connection getConnection(String dbDriver, String url, String userid, String password) throws SQLException,ClassNotFoundException, InstantiationException, IllegalAccessException{
        Driver driver = (Driver) Class.forName(dbDriver).newInstance();
        DriverManager.registerDriver(driver);
        return DriverManager.getConnection(url, userid, password);
    }
    public static Connection getOracleConnection(String ip, String port, String serviceName, String userid, String password, boolean isAuto) throws Exception{
        Connection connection = DBDialect.ORACLE.get().getConnection(ip, Integer.parseInt(port), serviceName, userid, password);
        connection.setAutoCommit(isAuto);
        return connection;
    }
    public static Connection getMySQLConnection(String ip, String port, String serviceName, String userid, String password, boolean isAuto) throws Exception{
        Connection connection = DBDialect.MYSQL.get().getConnection(ip, Integer.parseInt(port), serviceName, userid, password);
        connection.setAutoCommit(isAuto);
        return connection;
    } 
    public static Connection getMSSQLConnection(String ip, String port, String serviceName, String userid, String password, boolean isAuto) throws Exception{
        Connection connection = DBDialect.MSSQL.get().getConnection(ip, Integer.parseInt(port), serviceName, userid, password);
        connection.setAutoCommit(isAuto);
        return connection;
    } 
    
    

  /**
   * 关闭一个数据库链接
   * @author  周成功
   * @version 2004/08/02
   * @param   conn 建立的链接
   * @throws Exception
   */
  public static void close(Connection conn) {
	try{
        if (conn != null) {
            if (!conn.isClosed()) {
                conn.close();
            }
            conn = null;
        }
	} catch(SQLException ex) {
	}
  }
  public static void close(PreparedStatement preStmt) {
	try{
        if (preStmt != null) {
            preStmt.close();
            preStmt = null;
        }
	} catch(SQLException ex) {
	}
  }
  public static void close(Statement stmt) {
	try{
      if(stmt!=null) {
        stmt.close();
        stmt =null;
      }
	} catch(SQLException ex) {
	}
  }
  public static void close(ResultSet rest) {
	try{
      if(rest!=null) {
        rest.close();
        rest =null;
      }
	} catch(SQLException ex) {
	}
  }
  public static void close(RecordSet rest) {
      if(rest!=null) {
        rest.clear();
        rest =null;
      }
  }

  /**
   * 提交数据库链接
   * @param conn Connection
   * @throws SQLException
   */
  public static void commit(Connection conn) {
	try{
      if( (conn != null)&&(!conn.isClosed()) ) {
          conn.commit();
      }
	} catch(SQLException ex) {
	}
  }
  /**
   * 回滚数据库链接
   * @param conn Connection
   * @throws SQLException
   */
  public static void rollback(Connection conn) {
	try{
      if( (conn != null)&&(!conn.isClosed()) ) {
          conn.rollback();
      }
	} catch(SQLException ex) {
	}
  }



  //////////////////  常用工具类方法 ///////////////////////////////
  /**
   * 执行一个sql，得到执行第一条记录的第一个字段的内容
   * @author  周成功
   * @version 2004/08/02
   * @param   conn 建立的链接
   * @param   sql  执行查询的串
   * @throws Exception
   */
  public static String getValue(Connection conn, String sql)throws SQLException{
    String ret ="";
    Statement stmt = null;
    ResultSet rest = null;
    try {
        logDebug.debug("DBUtilJB.getValue.sql :: "+sql);
        stmt	=conn.createStatement();
        rest	=stmt.executeQuery(sql);
        if (rest.next()) {
           ret =rest.getString(1);
        }
    }finally{
        close(rest);
        close(stmt);
    }
    return ret;
  }
  public static String getValue(String dsn, String sql)throws SQLException,NamingException{
      Connection conn =null;
      try {
          conn =getConnection(dsn);
          return getValue(conn, sql);
      } finally {
          DBUtils.close(conn);
      }
  }
  public static String getValue(Connection conn, String sql, String[] parameter) throws SQLException {
      String ret = "";
      PreparedStatement preStmt = null;
      ResultSet rest = null;
      try {
          logDebug.debug("DBUtilJB.execute.sql :: "+sql);
          logDebug.debug("DBUtilJB.getValue.value :: "+ArrayUtils.toString(parameter));
          preStmt = conn.prepareStatement(sql);
          if (parameter != null) {
              for (int i = 0; i < parameter.length; i++) {
                  preStmt.setString(i+1, parameter[i]);
              }
          }
          rest =preStmt.executeQuery();
          if (rest.next()) {
             ret =rest.getString(1);
          }
      } finally {
          close(rest);
          close(preStmt);
      }
      return ret;
  }
  
  public static String getOracleSysdate(Connection conn, String format) throws SQLException {
      String sql="select to_char(sysdate, '"+format+"') from dual";
      return getValue(conn, sql);
  }  
  
  /**
   * 执行一个sql，得到结果集的第一个字段的内容。
   *
   * @param conn
   * @param sql
   * @param parameter
   * @return
   * @throws Exception
   */
  public static String[] getValues(Connection conn, String sql, String[] parameter) throws Exception {
      String[] ret =new String[0];
      RecordSet record = null;
      try {
          logDebug.debug("DBUtilJB.execute.sql :: "+sql);
          record = executeQueryToRecordSet(conn, sql, parameter);
          ret = new String[record.getRowCount()];
          for (int i = 0; i < record.getRowCount(); i++) {
              ret[i] = record.getString(i+1, 1);
          }
      } finally {
          close(record);
      }
      return ret;
  }  

  
  // 执行查询
  // 带参数执行SQL
  // parameter修补参数SQL的记录行，其中每个对象是一个Object类型数组，表示对一条记录的对应修补值。
  public static RecordSet[] executeQueryToRecordSet(Connection conn, String sql, String[][] parameter, int beginRow, int rowNum) throws SQLException {
      PreparedStatement preStmt = null;
      RecordSet[] result = new RecordSet[parameter==null?0:parameter.length];
      ResultSet rest = null;
      try {
          logDebug.debug("DBUtilJB.executeQuery.sql :: "+sql);
          preStmt = conn.prepareStatement(sql);
          if (parameter != null) {
              logDebug.debug("DBUtilJB.executeQuery.value :: "+ArrayUtils.toString(parameter));
              for (int i = 0; i < parameter.length; i++) {
                  for (int j = 0; j < parameter[i].length; j++) {
                      preStmt.setString(j + 1, parameter[i][j]);
                  }
                  rest =preStmt.executeQuery();
                  result[i] = new RecordSet(rest, beginRow, rowNum);
                  if(logDebug.isDebugEnabled()){
                      logDebug.debug("executeQuery.RecordSet.["+i+"].列数 :: " + result[i].getColumnCount());
                      logDebug.debug("executeQuery.RecordSet.["+i+"].行数 :: " + result[i].getRowCount());
                  }
              }
          }
          return result;
      } finally {
          close(rest);
          close(preStmt);
      }
  }
  public static RecordSet[] executeQueryToRecordSet(Connection conn, String sql, String[][] parameter) throws SQLException {
      return executeQueryToRecordSet(conn, sql, parameter, 1, -1);
  }
  public static RecordSet executeQueryToRecordSet(Connection conn, String sql, String[] parameter, int beginRow, int rowNum) throws SQLException {
      RecordSet[] result =executeQueryToRecordSet(conn, sql, new String[][]{parameter}, beginRow, rowNum);
      return ((result==null)||(result.length==0))?null:result[0];
  }
  public static RecordSet executeQueryToRecordSet(Connection conn, String sql, String[] parameter) throws SQLException {
      return executeQueryToRecordSet(conn, sql, parameter, 1, -1);
  }
  public static RecordSet executeQueryToRecordSet(Connection conn, String sql, int beginRow, int rowNum) throws  SQLException {
      Statement stmt = null;
      ResultSet rest = null;
      try {
          logDebug.debug("DBUtilJB.executeQuery.sql :: "+sql);
          stmt = conn.createStatement();
          rest =stmt.executeQuery(sql);
          RecordSet result = new RecordSet(rest, beginRow, rowNum);
          if(logDebug.isDebugEnabled()){
              logDebug.debug("executeQuery.RecordSet.列数 :: " + result.getColumnCount());
              logDebug.debug("executeQuery.RecordSet.行数 :: " + result.getRowCount());
          }
          return result;
      } finally {
          close(rest);
          close(stmt);
      }
  }
  public static RecordSet executeQueryToRecordSet(Connection conn, String sql) throws SQLException {
    return executeQueryToRecordSet(conn, sql, 1, -1);
  }  
  
  public static int execute(Connection conn, String sql, String[][] parameter) throws SQLException {
      int ret =0;
      PreparedStatement preStmt = null;
      try {
          if ((parameter!=null)&&(parameter.length>0)) {
              preStmt = conn.prepareStatement(sql);
              for (int i = 0; i < parameter.length; i++) {
                  String debugSql =sql;
                  logDebug.debug("DBUtilJB.execute.sql["+i+"] :: " + debugSql);
                  logDebug.debug("DBUtilJB.execute.value["+i+"] :: " + ArrayUtils.toString(parameter[i]));
                  for (int j = 0; j < parameter[i].length; j++) {
                      preStmt.setString(j+1, parameter[i][j]);
                  }
                  ret =ret + preStmt.executeUpdate();
              }
          } else {
              return execute(conn, sql);
          }
          return ret;
      } finally {
          close(preStmt);
      }
  }
  public static int execute(Connection conn, String sql, Object[] parameter) throws SQLException {
      PreparedStatement preStmt = null;
      try {
          if ((parameter!=null)&&(parameter.length>0)) {
              preStmt = conn.prepareStatement(sql);
              logDebug.debug("DBUtilJB.execute.sql :: " + sql);
              logDebug.debug("DBUtilJB.execute.value :: " + ArrayUtils.toString(parameter));
              for (int i = 0; i < parameter.length; i++) {
                  preStmt.setObject(i+1, parameter[i]);
                  /*
                  if( Byte.class.equals(parameter[i].getClass()) ) {
                      preStmt.setByte(i+1, (byte)parameter[i]);
                  } else {
                      preStmt.setString(i+1, FormatUtils.toString(parameter[i]));
                  }*/
              }
              return preStmt.executeUpdate();
          } else {
              return execute(conn, sql);
          }
      } finally {
          close(preStmt);
      }
  }  
  public static int execute(Connection conn, String sql) throws SQLException {
      PreparedStatement preStmt = null;
      try {
          logDebug.debug("DBUtilJB.execute.sql :: "+sql);
          preStmt = conn.prepareStatement(sql);
          return preStmt.executeUpdate();
      } finally {
          close(preStmt);
      }
  }
  // 使用静默的方式执行SQL
  public static int executeQuietly(Connection conn, String sql) {
      Statement stmt = null;
      try {
          logDebug.debug("DBUtilJB.executeQuietly.sql :: "+sql);
          stmt = conn.createStatement();
          return stmt.executeUpdate(sql);
      } catch (Exception e) {
          return -1;
      } finally {
          close(stmt);
      }
  }
  
  
  // 判断数据库表是否存在
  public static boolean isTableExist(Connection conn, String tablename) throws Exception {
      if(StringUtils.isEmpty(tablename)) {
          return false;
      }
      String sql = "select TABLE_NAME from user_tables t where t.TABLE_NAME=?";
      String ret = DBUtils.getValue(conn, sql, new String[]{tablename.toUpperCase()});
      if(StringUtils.isEmpty(ret)) {
          return false;
      } else {
          return true;
      }
  }
  
  // 获得表结构
  public static String buildDDL(Connection conn, String type, String name) throws Exception {
      String sql = "SELECT DBMS_METADATA.GET_DDL('" + type + "','" + name + "') FROM DUAL";
      String v = DBUtils.getValue(conn, sql);
      v = v.replaceAll("\n", "");
      if(OBJECT_TYPE_TABLE.equals(type)) {
          v = v.replaceFirst("CREATE TABLE \"[A-Z]+\".", "CREATE TABLE ");
          v = v.replaceFirst("USING INDEX .+", ")");
      } else if(OBJECT_TYPE_VIEW.equals(type)) {
          v = v.replaceFirst("CREATE OR REPLACE FORCE VIEW \"[A-Z]+\".", "CREATE OR REPLACE FORCE VIEW ");
      }
      return v;
  }
  
  
  public static String buildSqlInsertTable(ResultSet result, String tablename) throws Exception {
      ResultSetMetaData meta = result.getMetaData();
      StringBuffer table = new StringBuffer(1024);
      table.append("INSERT INTO ").append(tablename).append(" VALUES (");
      for (int i = 1; i <= meta.getColumnCount(); i++) {
          table.append("?");
          if(i < meta.getColumnCount()) {
              table.append(",");
          }
      }
      table.append(")");
      return table.toString();
  }
  
  public static final String OBJECT_TYPE_TABLE = "TABLE";
  public static final String OBJECT_TYPE_VIEW = "VIEW";
  
  // 复制表
  public static void copy(Connection sour, Connection desc, String typeObj, String tablename) throws Exception {
      PreparedStatement insertStmt = null;
      ResultSet result = null;
      try {
          result = sour.createStatement().executeQuery("SELECT * FROM "+tablename);
          Statement dropStmt = desc.createStatement();
          try {
              dropStmt.execute("DROP TABLE "+tablename);
          } catch(Exception e) {
          } finally {
              DBUtils.close(dropStmt);
          }
          Statement createStmt = desc.createStatement();
          try {
              createStmt.execute(buildDDL(sour, typeObj, tablename));
          } finally {
              DBUtils.close(createStmt);
          }
          
          if(OBJECT_TYPE_TABLE.equals(typeObj)) {
              insertStmt = desc.prepareStatement(buildSqlInsertTable(result, tablename));
              ResultSetMetaData meta = result.getMetaData();
              while(result.next()) {
                  for (int i = 1; i <= meta.getColumnCount(); i++) {
                      String type = meta.getColumnTypeName(i);
                      if(type.equals("DATE")||type.equals("TIMESTAMP")) {
                          insertStmt.setTimestamp(i, result.getTimestamp(i));
                      } else {
                          String value = result.getString(i);
                          insertStmt.setString(i, value);
                      }
                  }
                  insertStmt.executeUpdate();
              }
          }
      } finally {
          DBUtils.close(result);
          DBUtils.close(insertStmt);
      }
  }
  
  
  // 执行使用;分割的批量SQL
  public static void executeSqlString(Connection conn, String sql) throws Exception {
      String[] items = sql.split(";");
      for (String item : items) {
          if(StringUtils.isNotBlank(item)) {
              DBUtils.execute(conn, item);
          }
      }
  }

  public static TableMeta toMeta(ResultSet rest) throws Exception {
      if(rest==null || rest.isClosed()) {
          return null;
      }
      ResultSetMetaData metas = rest.getMetaData();
      TableMeta meta = new TableMeta(metas);
      return meta;
  }
  
  
  // 处理CLOB和BLOB字段
  //private static OracleLobHandler handler = new OracleLobHandler();
  private static DefaultLobHandler handler = new DefaultLobHandler();
  private static LobCreator creator = handler.getLobCreator();
  public static void setBlobAsBytes(PreparedStatement ps, int paramIndex, byte[] content) throws SQLException {
      creator.setBlobAsBytes(ps, paramIndex, content);
  }
  public static void setBlobAsBinaryStream(PreparedStatement ps, int paramIndex, final InputStream binaryStream, int contentLength) throws SQLException {
      creator.setBlobAsBinaryStream(ps, paramIndex, binaryStream, contentLength);
  }
  public static void setClobAsString(PreparedStatement ps, int paramIndex, final String content) throws SQLException {
      creator.setClobAsString(ps, paramIndex, content);
  }
  public static byte[] getBlobAsBytes(ResultSet rs, int columnIndex) throws SQLException {
      return handler.getBlobAsBytes(rs, columnIndex);
  }
  public static byte[] getBlobAsBytes(ResultSet rs, String columnName) throws SQLException {
      return handler.getBlobAsBytes(rs, columnName);
  }
  public static InputStream getBlobAsBinaryStream(ResultSet rs, int columnIndex) throws SQLException {
      return handler.getBlobAsBinaryStream(rs, columnIndex);
  }
  public static InputStream getBlobAsBinaryStream(ResultSet rs, String columnName) throws SQLException {
      return handler.getBlobAsBinaryStream(rs, columnName);
  }
  public static String getClobAsString(ResultSet rs, int columnIndex) throws SQLException {
      return handler.getClobAsString(rs, columnIndex);
  }
  public static String getClobAsString(ResultSet rs, String columnName) throws SQLException {
      return handler.getClobAsString(rs, columnName);
  }  
 
  
  
}
